using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;

namespace Lzfy_His_Service.Repositories
{
    public class RefundAttachRepository : IRefundAttachRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public RefundAttachRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<RefundAttach?> GetByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                rel_id AS RelId,
                input_date AS InputDate,
                attach_name AS AttachName,
                attach_id AS AttachId,
                attach_files AS AttachFiles,
                file_paths as FilePaths,
                file_id as FileId
                FROM lzfy_refund_attach WHERE id = @Id";
            return await connection.QueryFirstOrDefaultAsync<RefundAttach>(sql, new { Id = id });
        }

        public async Task<IEnumerable<RefundAttach>> GetByRelIdAsync(string relId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                rel_id AS RelId,
                input_date AS InputDate,
                attach_name AS AttachName,
                attach_id AS AttachId,
                attach_files AS AttachFiles,
                file_paths as FilePaths,
                file_id as FileId
                FROM lzfy_refund_attach WHERE rel_id = @RelId ORDER BY input_date";
            return await connection.QueryAsync<RefundAttach>(sql, new { RelId = relId });
        }

        public async Task<IEnumerable<RefundAttach>> GetAllAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                rel_id AS RelId,
                input_date AS InputDate,
                attach_name AS AttachName,
                attach_id AS AttachId,
                attach_files AS AttachFiles,
                file_paths as FilePaths,
                file_id as FileId
                FROM lzfy_refund_attach ORDER BY input_date DESC";
            return await connection.QueryAsync<RefundAttach>(sql);
        }

        public async Task<int> CreateAsync(RefundAttach refundAttach)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                DECLARE @NewId int;
                SELECT @NewId = ISNULL(MAX(id), 0) + 1 FROM lzfy_refund_attach;
                INSERT INTO lzfy_refund_attach (id, attach_name, attach_id, attach_files, rel_id, file_paths, file_id)
                VALUES (@NewId, @AttachName, @AttachId, @AttachFiles, @RelId, @FilePaths, @FileId);
                SELECT @NewId;";
            
            return await connection.QuerySingleAsync<int>(sql, refundAttach);
        }

        public async Task<bool> UpdateAsync(RefundAttach refundAttach)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE lzfy_refund_attach SET 
                    attach_name = @AttachName, 
                    attach_id = @AttachId, 
                    attach_files = @AttachFiles,
                    file_paths = @FilePaths,
                    file_id = @FileId
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, refundAttach);
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "DELETE FROM lzfy_refund_attach WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteByRelIdAsync(string relId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "DELETE FROM lzfy_refund_attach WHERE rel_id = @RelId";
            var rowsAffected = await connection.ExecuteAsync(sql, new { RelId = relId });
            return rowsAffected > 0;
        }
    }
}